import pymysql
import sys
from docx import Document
import datetime
from docx.shared import Pt
from docx.oxml.ns import qn


class MySQLMonitor():
    def __init__(self, host, port, user, password, database):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.conn = None
        self.cursor = None

    def dbconn(self):
        try: 
            self.conn = pymysql.connect(
                host=self.host, 
                port=self.port, 
                user=self.user, 
                password=self.password, 
                database=self.database,
                charset='utf8mb4'
            )
            self.cursor = self.conn.cursor()
        except Exception as e: 
            print('数据库连接失败')
            print(e)
            sys.exit()

    def close_conn(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
    
    def get_status(self):
        status = {}
        
        if not self.conn:
            self.dbconn()
            
        try:
            # 获取数据库运行时长
            self.cursor.execute("show global status where Variable_name='Uptime'")
            uptime = self.cursor.fetchone()
            status['运行时长(秒)'] = uptime[1]

            # 获取数据库当前连接数
            self.cursor.execute("show global status where Variable_name='Threads_connected'")
            threads_connected = self.cursor.fetchone()
            status['当前连接数'] = threads_connected[1]
            
            # 获取总查询次数
            self.cursor.execute("show global status where Variable_name='Questions'")
            questions = self.cursor.fetchone()
            status['总查询次数'] = questions[1]
            
            # 获取SELECT查询次数
            self.cursor.execute("show global status where Variable_name='Com_select'")
            com_select = self.cursor.fetchone()
            status['SELECT查询次数'] = com_select[1]
            
            # 添加更多有用的状态信息
            self.cursor.execute("show global status where Variable_name='Slow_queries'")
            slow_queries = self.cursor.fetchone()
            status['慢查询次数'] = slow_queries[1]
            
            self.cursor.execute("show global status where Variable_name='Innodb_buffer_pool_reads'")
            buffer_pool_reads = self.cursor.fetchone()
            status['InnoDB缓冲池读取次数'] = buffer_pool_reads[1]
            
        except Exception as e:
            print(f"获取状态失败: {e}")
            
        return status

    def set_chinese_font(self, paragraph):
        """设置中文字体"""
        for run in paragraph.runs:
            run.font.name = '微软雅黑'
            run._element.rPr.rFonts.set(qn('w:eastAsia'), '微软雅黑')

    def write_dict_to_word(self, data_dict, filename):
        # 创建一个新的Word文档
        doc = Document()
        
        # 设置全局字体（对默认样式）
        style = doc.styles['Normal']
        font = style.font
        font.name = '微软雅黑'
        font.size = Pt(10)
        
        # 添加标题
        newtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        title = doc.add_heading('MySQL数据库巡检报告', level=1)
        self.set_chinese_font(title)
        
        # 添加报告信息
        info_paragraph = doc.add_paragraph()
        info_paragraph.add_run('巡检时间: ').bold = True
        info_paragraph.add_run(newtime)
        self.set_chinese_font(info_paragraph)
        
        info_paragraph2 = doc.add_paragraph()
        info_paragraph2.add_run('数据库: ').bold = True
        info_paragraph2.add_run(f'{self.database}@{self.host}:{self.port}')
        self.set_chinese_font(info_paragraph2)
        
        doc.add_paragraph()  # 空行
        
        # 添加表格
        table = doc.add_table(rows=1, cols=2)
        table.style = 'Light Grid Accent 1'  # 使用内置表格样式
        
        # 设置表头
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '巡检项目'
        hdr_cells[1].text = '数值'
        
        # 设置表头字体
        for cell in hdr_cells:
            for paragraph in cell.paragraphs:
                self.set_chinese_font(paragraph)
                for run in paragraph.runs:
                    run.bold = True
        
        # 填充数据
        for key, value in data_dict.items():
            row_cells = table.add_row().cells
            row_cells[0].text = str(key)
            row_cells[1].text = str(value)
            
            # 设置表格内容字体
            for cell in row_cells:
                for paragraph in cell.paragraphs:
                    self.set_chinese_font(paragraph)
        
        # 添加说明段落
        doc.add_paragraph()
        note_paragraph = doc.add_paragraph()
        note_paragraph.add_run('说明: ').bold = True
        note_paragraph.add_run('本报告为MySQL数据库常规巡检结果')
        self.set_chinese_font(note_paragraph)
        
        # 保存文档
        doc.save(filename)
        print(f"巡检报告已成功生成: {filename}")


if __name__ == '__main__':
    try:
        # 创建数据库连接对象 - 请修改为您的实际数据库信息
        monitor = MySQLMonitor('localhost', 3306, 'root', '123456', 'mysql')
        
        # 获取状态数据
        status_data = monitor.get_status()
        
        # 生成报告
        monitor.write_dict_to_word(status_data, 'mysql_status_report.docx')
        
        print("数据库巡检完成！")
        
    except Exception as e:
        print(f"程序执行失败: {e}")
    finally:
        # 确保关闭数据库连接
        if 'monitor' in locals():
            monitor.close_conn()